using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using SMS.Common;
using SMS.Common.Data;
using SMS.Framework;

namespace SMS.SalesManagement
{
    public class DataAccess
    {

        public static void FillForecastTable(ref DSMain dataSet, DateTime date)
        {
            using (DbConnection conn = SQLite.GetDbConnection())
            {
                conn.ConnectionString = Context.Current.AppInfo.LocalDbConnectionString;//"data source=./LocalData/Local.db3";//SMS.Framework.Context.Current.AppInfo.LocalDbConnectionString;
                conn.Open();
                using (DbDataAdapter da = SQLite.GetDbDataAdapter())
                {
                    da.SelectCommand = conn.CreateCommand();
                    DateTime beginDate = new DateTime(date.Year, date.Month, 1);
                    DateTime endDate = beginDate.AddMonths(1).AddDays(-1);
                    da.SelectCommand.CommandText = "select * from tbl_sm_forecast where cdate between '" + beginDate.ToString("yyyy-MM-dd") + "' and '" + endDate.ToString("yyyy-MM-dd") + "'";
                    da.Fill(dataSet.Tables["Forecast"]);
                }
                conn.Close();
            }
        }

        public static bool SaveForecastItem(DataRow dr,bool isNew)
        {
            using (DbConnection conn = SQLite.GetDbConnection())
            {
                conn.ConnectionString = Context.Current.AppInfo.LocalDbConnectionString; //"data source=./LocalData/Local.db3";//SMS.Framework.Context.Current.AppInfo.LocalDbConnectionString;
                conn.Open();
                int rowCount = 0;
                using (DbCommand comm = conn.CreateCommand())
                {
                    comm.CommandType = CommandType.Text;
                    if (isNew)
                    {
                        comm.CommandText = "insert into tbl_sm_forecast (cdate,b_sales,b_tc,t_sales,t_tc,t_dlv_sales,t_dlv_tc,t_labor_hour,t_electricity,t_water,t_gas,status) values (@cdate,@b_sales,@b_tc,@t_sales,@t_tc,@t_dlv_sales,@t_dlv_tc,@t_labor_hour,@t_electricity,@t_water,@t_gas,@status)";
                    }
                    else
                    {
                        comm.CommandText = "update tbl_sm_forecast set b_sales=@b_sales,b_tc=@b_tc,t_sales=@t_sales,t_tc=@t_tc,t_dlv_sales=@t_dlv_sales,t_dlv_tc=@t_dlv_tc,t_labor_hour=@t_labor_hour,t_electricity=@t_electricity,t_water=@t_water,t_gas=@t_gas,status=@status where cdate=@cdate";
                    }

                    #region Create Parameters for two sql statement below
                    comm.Parameters.Clear();
                    DbParameter pdate = comm.CreateParameter();
                    DbParameter bsales = comm.CreateParameter();
                    DbParameter tsales = comm.CreateParameter();
                    DbParameter tdsales = comm.CreateParameter();
                    DbParameter btc = comm.CreateParameter();
                    DbParameter ttc = comm.CreateParameter();
                    DbParameter tdtc = comm.CreateParameter();
                    DbParameter laborhour = comm.CreateParameter();
                    DbParameter electricity = comm.CreateParameter();
                    DbParameter water = comm.CreateParameter();
                    DbParameter gas = comm.CreateParameter();
                    DbParameter status = comm.CreateParameter();

                    pdate.ParameterName = "@cdate";
                    bsales.ParameterName = "@b_sales";
                    tsales.ParameterName = "@t_sales";
                    tdsales.ParameterName = "@t_dlv_sales";
                    btc.ParameterName = "@b_tc";
                    ttc.ParameterName = "@t_tc";
                    tdtc.ParameterName = "@t_dlv_tc";
                    laborhour.ParameterName = "@t_labor_hour";
                    electricity.ParameterName = "@t_electricity";
                    water.ParameterName = "@t_water";
                    gas.ParameterName = "@t_gas";
                    status.ParameterName = "@status";

                    pdate.DbType = DbType.Date;
                    bsales.DbType = DbType.Double;
                    tsales.DbType = DbType.Double;
                    tdsales.DbType = DbType.Double;
                    btc.DbType = DbType.Int16;
                    ttc.DbType = DbType.Int16;
                    tdtc.DbType = DbType.Int16;
                    laborhour.DbType = DbType.Double;
                    electricity.DbType = DbType.Int16;
                    water.DbType = DbType.Int16;
                    gas.DbType = DbType.Int16;
                    status.DbType = DbType.String;

                    pdate.Value = dr["cdate"];
                    bsales.Value = dr["b_sales"];
                    tsales.Value = dr["t_sales"];
                    tdsales.Value = dr["t_dlv_sales"];
                    btc.Value = dr["b_tc"];
                    ttc.Value = dr["t_tc"];
                    tdtc.Value = dr["t_dlv_tc"];
                    laborhour.Value = dr["t_labor_hour"];
                    electricity.Value = dr["t_electricity"];
                    water.Value = dr["t_water"];
                    gas.Value = dr["t_gas"];
                    status.Value = dr["status"];

                    comm.Parameters.Add(pdate);
                    comm.Parameters.Add(bsales);
                    comm.Parameters.Add(tsales);
                    comm.Parameters.Add(tdsales);
                    comm.Parameters.Add(btc);
                    comm.Parameters.Add(ttc);
                    comm.Parameters.Add(tdtc);
                    comm.Parameters.Add(laborhour);
                    comm.Parameters.Add(electricity);
                    comm.Parameters.Add(water);
                    comm.Parameters.Add(gas);
                    comm.Parameters.Add(status);

                    #endregion

                    string newSql = ReplaceParas(comm.CommandText, comm.Parameters);
                    comm.CommandText = newSql;

                    rowCount = comm.ExecuteNonQuery();
                }
                conn.Close();

                if (rowCount > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public static string ReplaceParas(string sqlString, DbParameterCollection dbParameterCollection)
        {
            string sql = sqlString;
            foreach (DbParameter para in dbParameterCollection)
            {
                switch (para.DbType)
                {
                    case DbType.Date:
                        sql = sql.Replace(para.ParameterName, "'" + (DateTime.Parse(para.Value.ToString())).ToString("yyyy-MM-dd") + "'");
                        break;
                    case DbType.DateTime:
                        sql = sql.Replace(para.ParameterName, "'" + (DateTime.Parse(para.Value.ToString())).ToString("yyyy-MM-dd HH:mm:ss") + "'");
                        break;
                    case DbType.Time:
                        sql = sql.Replace(para.ParameterName, "'" + (DateTime.Parse(para.Value.ToString())).ToString("HH:mm:ss") + "'");
                        break;
                    case DbType.Double:
                        sql = sql.Replace(para.ParameterName, para.Value.ToString());
                        break;
                    case DbType.Int16:
                        sql = sql.Replace(para.ParameterName, para.Value.ToString());
                        break;
                    case DbType.Int32:
                        sql = sql.Replace(para.ParameterName, para.Value.ToString());
                        break;
                    case DbType.Int64:
                        sql = sql.Replace(para.ParameterName, para.Value.ToString());
                        break;
                    case DbType.Boolean:
                        sql = sql.Replace(para.ParameterName, para.Value.ToString());
                        break;
                    default:
                        sql = sql.Replace(para.ParameterName, "'" + para.Value.ToString() + "'");
                        break;
                }
            }
            return sql;
        }

        public static void FillOperationLogTable(ref DSMain dataSet, DateTime date)
        {
            using (DbConnection conn = SQLite.GetDbConnection())
            {
                conn.ConnectionString = "data source=./LocalData/Local.db3";//SMS.Framework.Context.Current.AppInfo.LocalDbConnectionString;
                conn.Open();
                using (DbDataAdapter da = SQLite.GetDbDataAdapter())
                {
                    da.SelectCommand = conn.CreateCommand();
                    DateTime beginDate = new DateTime(date.Year, date.Month, 1);
                    DateTime endDate = beginDate.AddMonths(1).AddDays(-1);
                    da.SelectCommand.CommandText = "select * from tbl_sm_opr_log where cdate between '" + beginDate.ToString("yyyy-MM-dd") + "' and '" + endDate.ToString("yyyy-MM-dd") + "'";
                    da.Fill(dataSet.Tables["OperationLog"]);
                }
                conn.Close();
            }
        }

        internal static bool SaveOperationLogItem(DataRow dr, bool isNew)
        {
            using (DbConnection conn = SQLite.GetDbConnection())
            {
                conn.ConnectionString = "data source=./LocalData/Local.db3";//SMS.Framework.Context.Current.AppInfo.LocalDbConnectionString;
                conn.Open();
                int rowCount = 0;
                using (DbCommand comm = conn.CreateCommand())
                {
                    comm.CommandType = CommandType.Text;
                    if (isNew)
                    {
                        comm.CommandText = "insert into tbl_sm_opr_log (cdate,ttl_sales,ttl_tc,di_sales,di_tc,ta_sales,ta_tc,dlv_sales,dlv_tc,labor_hour,electricity,water,gas,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,opr_rpt,mkt_rpt,rpr_rpt,evt_rpt,status) values (@cdate,@ttl_sales,@ttl_tc,@di_sales,@di_tc,@ta_sales,@ta_tc,@dlv_sales,@dlv_tc,@labor_hour,@electricity,@water,@gas,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@opr_rpt,@mkt_rpt,@rpr_rpt,@evt_rpt,@status)";
                    }
                    else
                    {
                        comm.CommandText = "update tbl_sm_opr_log set ttl_sales=@ttl_sales,ttl_tc=@ttl_tc,di_sales=@di_sales,di_tc=@di_tc,ta_sales=@ta_sales,ta_tc=@ta_tc,dlv_sales=@dlv_sales,dlv_tc=@dlv_tc,labor_hour=@labor_hour,electricity=@electricity,water=@water,gas=@gas,p1=@p1,p2=@p2,p3=@p3,p4=@p4,p5=@p5,p6=@p6,p7=@p7,p8=@p8,p9=@p9,p10=@p10,opr_rpt=@opr_rpt,mkt_rpt=@mkt_rpt,rpr_rpt=@rpr_rpt,evt_rpt=@evt_rpt,status=@status where cdate=@cdate";
                    }

                    #region Create Parameters for two sql statement below
                    comm.Parameters.Clear();
                    DbParameter cdate = comm.CreateParameter();
                    DbParameter ttl_sales = comm.CreateParameter();
                    DbParameter ttl_tc = comm.CreateParameter();
                    DbParameter di_sales = comm.CreateParameter();
                    DbParameter di_tc = comm.CreateParameter();
                    DbParameter ta_sales = comm.CreateParameter();
                    DbParameter ta_tc = comm.CreateParameter();
                    DbParameter dlv_sales = comm.CreateParameter();
                    DbParameter dlv_tc = comm.CreateParameter();
                    DbParameter labor_hour = comm.CreateParameter();
                    DbParameter electricity = comm.CreateParameter();
                    DbParameter water = comm.CreateParameter();
                    DbParameter gas = comm.CreateParameter();
                    DbParameter p1 = comm.CreateParameter();
                    DbParameter p2 = comm.CreateParameter();
                    DbParameter p3 = comm.CreateParameter();
                    DbParameter p4 = comm.CreateParameter();
                    DbParameter p5 = comm.CreateParameter();
                    DbParameter p6 = comm.CreateParameter();
                    DbParameter p7 = comm.CreateParameter();
                    DbParameter p8 = comm.CreateParameter();
                    DbParameter p9 = comm.CreateParameter();
                    DbParameter p10 = comm.CreateParameter();
                    DbParameter opr_rpt = comm.CreateParameter();
                    DbParameter mkt_rpt = comm.CreateParameter();
                    DbParameter rpr_rpt = comm.CreateParameter();
                    DbParameter evt_rpt = comm.CreateParameter();
                    DbParameter status = comm.CreateParameter();

                    cdate.ParameterName = "@cdate";
                    ttl_sales.ParameterName = "@ttl_sales";
                    ttl_tc.ParameterName = "@ttl_tc";
                    di_sales.ParameterName = "@di_sales";
                    di_tc.ParameterName = "@di_tc";
                    ta_sales.ParameterName = "@ta_sales";
                    ta_tc.ParameterName = "@ta_tc";
                    dlv_sales.ParameterName = "@dlv_sales";
                    dlv_tc.ParameterName = "@dlv_tc";
                    labor_hour.ParameterName = "@labor_hour";
                    electricity.ParameterName = "@electricity";
                    water.ParameterName = "@water";
                    gas.ParameterName = "@gas";
                    p1.ParameterName = "@p1";
                    p2.ParameterName = "@p2";
                    p3.ParameterName = "@p3";
                    p4.ParameterName = "@p4";
                    p5.ParameterName = "@p5";
                    p6.ParameterName = "@p6";
                    p7.ParameterName = "@p7";
                    p8.ParameterName = "@p8";
                    p9.ParameterName = "@p9";
                    p10.ParameterName = "@p10";
                    opr_rpt.ParameterName = "@opr_rpt";
                    mkt_rpt.ParameterName = "@mkt_rpt";
                    rpr_rpt.ParameterName = "@rpr_rpt";
                    evt_rpt.ParameterName = "@evt_rpt";
                    status.ParameterName = "@status";

                    cdate.DbType = DbType.Date;
                    ttl_sales.DbType = DbType.Double;
                    ttl_tc.DbType = DbType.Int16;
                    di_sales.DbType = DbType.Double;
                    di_tc.DbType = DbType.Int16;
                    ta_sales.DbType = DbType.Double;
                    ta_tc.DbType = DbType.Int16;
                    dlv_sales.DbType = DbType.Double;
                    dlv_tc.DbType = DbType.Int16;
                    labor_hour.DbType = DbType.Double;
                    electricity.DbType = DbType.Int16;
                    water.DbType = DbType.Int16;
                    gas.DbType = DbType.Int16;
                    p1.DbType = DbType.Int16;
                    p2.DbType = DbType.Int16;
                    p3.DbType = DbType.Int16;
                    p4.DbType = DbType.Int16;
                    p5.DbType = DbType.Int16;
                    p6.DbType = DbType.Int16;
                    p7.DbType = DbType.Int16;
                    p8.DbType = DbType.Int16;
                    p9.DbType = DbType.Int16;
                    p10.DbType = DbType.Int16;
                    opr_rpt.DbType = DbType.String;
                    mkt_rpt.DbType = DbType.String;
                    rpr_rpt.DbType = DbType.String;
                    evt_rpt.DbType = DbType.String;
                    status.DbType = DbType.String;

                    cdate.Value = dr["cdate"];
                    ttl_sales.Value = dr["ttl_sales"];
                    ttl_tc.Value = dr["ttl_tc"];
                    di_sales.Value = dr["di_sales"];
                    di_tc.Value = dr["di_tc"];
                    ta_sales.Value = dr["ta_sales"];
                    ta_tc.Value = dr["ta_tc"];
                    dlv_sales.Value = dr["dlv_sales"];
                    dlv_tc.Value = dr["dlv_tc"];
                    labor_hour.Value = dr["labor_hour"];
                    electricity.Value = dr["electricity"];
                    water.Value = dr["water"];
                    gas.Value = dr["gas"];
                    p1.Value = dr["p1"];
                    p2.Value = dr["p2"];
                    p3.Value = dr["p3"];
                    p4.Value = dr["p4"];
                    p5.Value = dr["p5"];
                    p6.Value = dr["p6"];
                    p7.Value = dr["p7"];
                    p8.Value = dr["p8"];
                    p9.Value = dr["p9"];
                    p10.Value = dr["p10"];
                    opr_rpt.Value = dr["opr_rpt"];
                    mkt_rpt.Value = dr["mkt_rpt"];
                    rpr_rpt.Value = dr["rpr_rpt"];
                    evt_rpt.Value = dr["evt_rpt"];
                    status.Value = dr["status"];

                    comm.Parameters.Add(cdate);
                    comm.Parameters.Add(ttl_sales);
                    comm.Parameters.Add(ttl_tc);
                    comm.Parameters.Add(di_sales);
                    comm.Parameters.Add(di_tc);
                    comm.Parameters.Add(ta_sales);
                    comm.Parameters.Add(ta_tc);
                    comm.Parameters.Add(dlv_sales);
                    comm.Parameters.Add(dlv_tc);
                    comm.Parameters.Add(labor_hour);
                    comm.Parameters.Add(electricity);
                    comm.Parameters.Add(water);
                    comm.Parameters.Add(gas);
                    comm.Parameters.Add(p1);
                    comm.Parameters.Add(p2);
                    comm.Parameters.Add(p3);
                    comm.Parameters.Add(p4);
                    comm.Parameters.Add(p5);
                    comm.Parameters.Add(p6);
                    comm.Parameters.Add(p7);
                    comm.Parameters.Add(p8);
                    comm.Parameters.Add(p9);
                    comm.Parameters.Add(p10);
                    comm.Parameters.Add(opr_rpt);
                    comm.Parameters.Add(mkt_rpt);
                    comm.Parameters.Add(rpr_rpt);
                    comm.Parameters.Add(evt_rpt);
                    comm.Parameters.Add(status);

                    #endregion

                    string newSql = ReplaceParas(comm.CommandText, comm.Parameters);
                    comm.CommandText = newSql;

                    rowCount = comm.ExecuteNonQuery();
                }
                conn.Close();

                if (rowCount > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
    }
}
